﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using E_Commerce.Domain.Interfaces;
using Dapper;
using Microsoft.Extensions.Configuration;
using E_Commerce.Domain.Entities;

namespace E_Commerce.Infrastructure.Data
{
   public class ProductRepository:
        DapperRepository<Product>,IProductRepository
    {
        
        /// <summary>
        /// 依赖注入
        /// </summary>
        private IConfiguration _configuration;
        public ProductRepository(IConfiguration configuration)
        {
            this._configuration = configuration;
            this.ConnStr = _configuration.GetConnectionString("ConnStr");
            SimpleCRUD.SetDialect(SimpleCRUD.Dialect.MySQL);
            Conn = new MySql.Data.MySqlClient.MySqlConnection(this.ConnStr);
        }
        #region 回收站
        /// <summary>
        /// 显示回收站
        /// </summary>
        /// <param name="Status"></param>
        /// <returns></returns>
        public List<Product> AgainAsync()
        {
            string sql = $"select * from Product where Status=0";
            return Conn.Query<Product>(sql).ToList();
        }
        /// <summary>
        /// 还原
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<int> DownStatusAsync(int id)
        {
            string sql = $"update Product set Status=1 where ProId={id}";
            return await Conn.ExecuteAsync(sql);
        }

        /// <summary>
        /// 加入回收站
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<int> UptStatusAsync(int id)
        {
            string sql = $"update Product set Status=0 where ProId={id}";
            return await Conn.ExecuteAsync(sql);
        }

        #endregion

        #region 商品
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id">主键</param>
        /// <returns></returns>

        public async Task<int> DeleAsync(int id)
        {
            string sql = $"delete from Product where ProId={id}";
            return await Conn.ExecuteAsync(sql);
        }
        

        /// <summary>
        /// 反填
        /// </summary>
        /// <param name="id">主键</param>
        /// <returns></returns>
        public async Task<Product> FillAsync(int id)
        {
            return await Conn.GetAsync<Product>(id);
        }


        /// <summary>
        /// 显示+查询+分页
        /// </summary>
        /// <param name="pageIndex">页索引</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="totalCount">总条数</param>
        /// <param name="pName">商品名称</param>
        /// <returns></returns>
        public List<Product> GetProductsAsync(int pageIndex, int pageSize,out int totalCount, string pName = "")
        {
            string sql = $"select p.ProId,p.ProName,p.Price,p.NewPrice,p.PicUrl,p.ProAdress,p.SheifLife,p.ProNum,p.Sort,c.CateName,p.Inventory,p.AddTime,p.Bought,p.Business,p.Status,p.Specifics from ProductCategory c right join Product p on p.Sort=c.CateId where 1 =1 and p.Status=1";
            if (!string.IsNullOrEmpty(pName))
            {
                sql += $" and p.ProName like '%{pName}%'";
            }
            sql += $"  limit @a1,@a2";
            var sqlcount = "select count(1) from Product";
            totalCount = Conn.ExecuteScalar<int>(sqlcount);
            return Conn.Query<Product>(sql,new { a1=(pageIndex-1)*pageSize,a2=pageSize}).ToList();
        }
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        public async Task<int> Insert(Product p)
        {
            return (int)await Conn.InsertAsync(p);
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        public async Task<int> UptAsync(Product p)
        {
            return await Conn.UpdateAsync(p);
        }
        #endregion

        #region 商品分类管理

        /// <summary>
        /// 商品详情
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ProductSpecification ProductdetailsAsync(int id)
        {
            string sql = $"SELECT * from ProductSpecification a join product b on a.ProId=b.ProId join Specification c on a.SpeciId=c.SpeciId where a.ProId={id}";
            return (ProductSpecification)Conn.Query<ProductSpecification>(sql);
        }
        /// <summary>
        /// 商品分类
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<ProductTypes> CommodityAsync(int id)
        {
            string sql = $"select * from ProductCategory c right join Product p on p.Sort=c.CateId where c.CateId={id}";
            return (ProductTypes)await Conn.QueryAsync<ProductTypes>(sql);
        }
        #endregion
    }
}
